# _*_ coding utf-8 _*_
# FILENAME：.py
# DESCRIPTION: 
# AUTH: 
# DATE: 2022/6/110:56 上午
import xlrd
import xlwt
import os
from PyQt5.QtWidgets import *
from PyQt5 import QtCore
from PyQt5.QtCore import QStringListModel


class UploadDialog(QDialog):
    def __init__(self, parent=None):
        super(UploadDialog, self).__init__(parent)
        self.init_ui(parent)

    def init_ui(self, parent):
        self.setWindowTitle("上传文件")
        self.resize(400, 600)
        self.cwd = os.getcwd()  # 文件路径
        self.dicts = {}
        # 定义元素
        self.file_name = QLineEdit()
        self.btn_close = QPushButton()
        self.btn_close.setText('关闭')
        self.btn_close.clicked.connect(self.btn_close_click)
        self.btn_openFile = QPushButton()
        self.btn_openFile.setText('选择')
        self.btn_openFile.clicked.connect(self.on_chooseFile)
        self.btn_start = QPushButton()
        self.btn_start.setText('开始')
        self.btn_start.clicked.connect(self.read_excel)
        self.listView = QListView()  # 创建一个listview对象
        self.slm = QStringListModel()  # 创建mode
        self.qList = []  # 添加的数组数据
        self.slm.setStringList(self.qList)  # 将数据设置到model
        self.listView.setModel(self.slm)  ##绑定 listView 和 model
        self.progressBar = QProgressBar(self)
        self.progressBar.setStyleSheet("""
            QProgressBar {   
                text-align: center; /*进度值居中*/
                border: 1px solid #2196F3;\n
                border-radius: 1px;   background-color: #FFFFFF;
                }
            QProgressBar::chunk {   
                background-color: #2196F3;
                width: 10px; /*区块宽度*/
                margin: 0.5px;
            }
        """
                                       )
        '''布局'''
        grid = QGridLayout()
        grid.addWidget(self.file_name, 0, 0, 1, 9)
        grid.addWidget(self.btn_openFile, 0, 9, 1, 1)
        grid.addWidget(self.progressBar, 1, 0, 1, 9)
        grid.addWidget(self.btn_start, 1, 9, 1, 1)
        grid.addWidget(self.listView, 5, 0, 1, 10)
        grid.addWidget(self.btn_close, 6, 9, 1, 1)
        self.setLayout(grid)

    def btn_close_click(self, parent):
        self.close()

    def cancel_btn_click(self):
        self.close()

    #  progress处理组件
    def load_progress_bar(self, index):
        val = index / ((len(self.qList) - 1) * 0.01)
        self.progressBar.setValue(val)

    #  数据解析日志
    def _log(self, val):
        self.qList.append(">>" + val)
        self.slm.setStringList(self.qList)

    #  自定义message
    def _messagebox(self, title, message):
        messageBox = QMessageBox()
        messageBox.setWindowTitle(title)
        messageBox.setText(message)
        messageBox.addButton(QPushButton('确定'), QMessageBox.YesRole)
        messageBox.exec_()

    #  读取excel
    def read_excel(self):
        file_path = self.file_name.text()
        if file_path == "":
            self._messagebox("告警", "请选择文件")
        else:
            try:
                workbook = xlrd.open_workbook(file_path)
                # 获取所有sheet
                # print(workbook.sheet_names())
                sheet = workbook.sheet_by_index(0)
                rows = sheet.nrows  # 行
                cols = sheet.ncols  # 列
            except Exception as e:
                print('数据源错误！')
            else:
                temps_dicts = {}
                self.qList.clear()  # 清空列表
                self.slm.setStringList(self.qList)
                self._log("准备开始，共计" + str(rows) + "行数据")
                self.progressBar.setValue(0)
                for i in range(0, rows):
                    self._log("正在解析第" + str(i+1) + "行数据")
                    self.load_progress_bar(i+1)
                    for c in range(0, cols):
                            # print(sheet.row(i)[3].value)
                            value = sheet.row(i)[c].value
                            print(value)

                # self.dicts.update(temps_dicts)
                # print(self.dicts)

    #  写入excel
    def write_excel(self):

        fileName, file_type = QFileDialog.getSaveFileName(self, "文件保存", "/", "文件 (*.xls,*.xlsx);")

        if fileName == "":
            return
        try:
            workbook = xlwt.Workbook()

            for key, value in self.jia_dicts.items():
                worksheet = workbook.add_sheet(key)
                i = 0
                for name, values in value.items():
                    j = 0
                    worksheet.write(i, j, name)
                    worksheet.write(i, j + 1, str(values[0]))
                    worksheet.write(i, j + 2, values[1])
                    i += 1

            workbook.save(fileName)
        except Exception as e:
            print(e)

    #  打开文件目录
    def on_chooseFile(self):
        fileName_choose, filetype = QFileDialog.getOpenFileName(self,
                                                                "选取文件",
                                                                self.cwd,  # 起始路径
                                                                "Text Files (*.xls, *.xlsx);;All Files (*)")  #  设置文件扩展名过滤,用双分号间隔

        if fileName_choose == "":
            return

        self.file_name.setText(fileName_choose)

    @staticmethod
    def get_dialog(parent=None):
        dialog = UploadDialog(parent)
        return dialog.exec()
